﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class CustomerDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        public static bool IsEmailTaken(string email)
        {
            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                var cmd = new SqlCommand("spIsCustomerEmailTaken", con) {CommandType = CommandType.StoredProcedure};
                var emailParameter = new SqlParameter("@Email", email);
                var returnCodeParameter = new SqlParameter("@ReturnCode", SqlDbType.Bit)
                {
                    Direction = ParameterDirection.Output
                };
                cmd.Parameters.Add(emailParameter);
                cmd.Parameters.Add(returnCodeParameter);
                cmd.ExecuteNonQuery();
                return Convert.ToBoolean(returnCodeParameter.Value);
            }
        }

        public static void GetCustomerType(Customer customer)
        {
            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                var cmd = new SqlCommand("spGetCustomerType", con) {CommandType = CommandType.StoredProcedure};
                var cusIdParameter = new SqlParameter("@CustomerId", customer.CustomerId);
                cmd.Parameters.Add(cusIdParameter);
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    customer.Type = rdr["Type"].ToString();
                }
            }
        }

        public static void GetCustomerId(Customer customer)
        {
            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                var cmd = new SqlCommand("spGetCustomerId", con) {CommandType = CommandType.StoredProcedure};
                var unameParameter = new SqlParameter("@Username", customer.Username);
                cmd.Parameters.Add(unameParameter);
                customer.CustomerId = (int) cmd.ExecuteScalar();
            }
        }

        public static void GetUsername(Customer customer)
        {
            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                var cmd = new SqlCommand("spGetUsername", con) {CommandType = CommandType.StoredProcedure};
                var unameParameter = new SqlParameter("@CustomerId", customer.CustomerId);
                cmd.Parameters.Add(unameParameter);
                var rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    customer.Username = rdr["Username"].ToString();
                }
            }
        }

        /// <summary>
        ///     Get customer by telephone number
        /// </summary>
        /// <param name="telephone">Represent's the customer's telephone number</param>
        /// <returns>Customer</returns>
        public static Customer GetCustomerDetails(string telephone)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetCustomerByTelephone", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@TelephoneNo", telephone);

                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Customer customer = new Customer
                        {
                            CustomerId = Convert.ToInt32(reader["CustomerId"]),
                            EMail = reader["EMail"].ToString(),
                            TelephoneNo = reader["TelephoneNo"].ToString(),
                            Type = reader["Type"].ToString(),
                            Username = reader["Username"].ToString()
                        };

                        if (reader["SubsidiaryId"] != DBNull.Value)
                        {
                            customer.SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]);
                        }

                        return customer;
                    }
                }
            }
            return null;
        }

        /// <summary>
        ///     Get All customers
        /// </summary>
        /// <returns>All customers</returns>
        public static List<Customer> GetAllCustomers()
        {
            List<Customer> customers = new List<Customer>();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetAllCustomers", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Customer customer = new Customer
                        {
                            CustomerId = Convert.ToInt32(reader["CustomerId"]),
                            EMail = reader["EMail"].ToString(),
                            TelephoneNo = reader["TelephoneNo"].ToString(),
                            Type = reader["Type"].ToString(),
                            Username = reader["Username"].ToString()
                        };

                        if (reader["SubsidiaryId"] != DBNull.Value)
                        {
                            customer.SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]);
                        }

                        customers.Add(customer);
                    }
                }
            }
            return customers;
        }

        /// <summary>
        ///     Get customer name by customer id
        /// </summary>
        /// <param name="customerId">Represent's the customer id</param>
        /// <returns>Customer's name (Personal customer - First name / Business customer - Company name)</returns>
        public static string GetCustomerName(int customerId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetCustomerNameByCustomerId", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@CustomerId", customerId);

                connection.Open();

                object name = command.ExecuteScalar();

                return name == null ? null : name.ToString();
            }
        }
    }
}